package dbLayer;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

import modelLayer.TeachingBlock;
import modelLayer.Staff;
import modelLayer.Subject;
import modelLayer.Room;

public class DBTeachingBlock {
	
	private Connection con;
	private DBSubject dbSubject = new DBSubject();
	private DBStaff dbStaff = new DBStaff();
	private DBRoom dbRoom = new DBRoom();
	private List<Integer> ExistingTB;
	
	public DBTeachingBlock () {
		this.con = DBConnection.getInstance().getDBcon();
	}
	
	public List<TeachingBlock> findTBRoomID (int room) {
		
		ResultSet results;
		List<TeachingBlock> matchingTBs = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM TachingBlocks WHERE Room = ?");
			statement.setInt(1, room);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				TeachingBlock tb = new TeachingBlock();
				tb = buildObject(results);
				System.out.println(tb);
				matchingTBs.add(tb);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		return matchingTBs;
	}
	
	public List<TeachingBlock> findTBSubjectID (int subject) {
		
		ResultSet results;
		List<TeachingBlock> matchingTBs = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM TachingBlocks WHERE Subject = ?");
			statement.setInt(1, subject);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				TeachingBlock tb = new TeachingBlock();
				tb = buildObject(results);
				System.out.println(tb);
				matchingTBs.add(tb);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		return matchingTBs;
	}
	
	public List<TeachingBlock> findTBStaffID (int staff) {
		
		ResultSet results;
		List<TeachingBlock> matchingTBs = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM TachingBlocks WHERE Staff = ?");
			statement.setInt(1, staff);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				TeachingBlock tb = new TeachingBlock();
				tb = buildObject(results);
				System.out.println(tb);
				matchingTBs.add(tb);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		return matchingTBs;
	}
	
	public TeachingBlock findTeachingBlockID (int ID) {
		
		ResultSet results;
		TeachingBlock tb = null;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM TeachingBlocks WHERE ID = ?");
			statement.setInt(1, ID);
			results = statement.executeQuery();
			
			while (results.next()) {
				tb = buildObject(results);
				System.out.println(tb);
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		return tb;
	}
	
	public List<TeachingBlock> getAllTeachingBlocks () {
		
		ResultSet results;
		List<TeachingBlock> matchingTeachingBlocks = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM TeachingBlocks");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while(results.next()){
				TeachingBlock tb = new TeachingBlock();
				tb = buildObject(results);
				System.out.println(tb);
				matchingTeachingBlocks.add(tb);
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception:" + e);
		}
		
		return matchingTeachingBlocks;
	}
	
	public boolean saveTeachingBlock (TeachingBlock tb) {
		
		boolean result = true;
		int resultCount = -1;
		
		if (!checkExistingTb(tb)) {
		
			try {
				PreparedStatement statement = con.prepareStatement("INSERT into TeachingBlocks values (?,?,?)");
				statement.setInt(1, tb.getStaff().getID());
				statement.setInt(2, tb.getSubject().getID());
				statement.setInt(3, tb.getRoom().getID());
				statement.setQueryTimeout(5);
				
				resultCount = statement.executeUpdate();
				
				statement.close();
			}
			catch (Exception e) {
				System.out.println("Exception: " + e);
			}
		
		}
		else {
			
			result = false;
			
		}
		
		return result;
	}
	
	public int higestID () {
		
		ResultSet results;
		
		int maxID = 0;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT MAX(ID) FROM TeachingBlocks");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				maxID = results.getInt(1);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception " + e);
		}
		
		return maxID;
		
	}
	
	public void deleteTeachingBlockID (int tbID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("DELETE FROM TeachingBlocks WHERE ID = ?");
			statement.setInt(1, tbID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}
	
	public boolean updateTeachingBlock (TeachingBlock tb) {
		
		boolean result = true;
		
		if (!checkExistingTb(tb)) {
		
			try {
				
				PreparedStatement statement = con.prepareStatement("UPDATE TeachingBlocks SET Staff = ?, Subject = ?, Room = ? WHERE ID = ?");
				statement.setInt(1, tb.getStaff().getID());
				statement.setInt(2, tb.getSubject().getID());
				statement.setInt(3, tb.getRoom().getID());
				statement.setInt(4, tb.getID());
				statement.setQueryTimeout(5);
				
				int resultCount = statement.executeUpdate();
				
				statement.close();
				
			}
			catch (Exception e) {
				System.out.println("Exception: " + e);
			}
		
		}
		else {
			
			result = false;
			
		}
		
		return result;
		
	}
	
	public boolean checkExistingTb (TeachingBlock tb) {
		
		ResultSet results;
		boolean result = false;
		ExistingTB = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM TeachingBlocks WHERE Staff = ? AND Subject = ? AND Room = ?");
			statement.setInt(1, tb.getStaff().getID());
			statement.setInt(2, tb.getSubject().getID());
			statement.setInt(3, tb.getRoom().getID());
			statement.setQueryTimeout(5);
			
			results = statement.executeQuery();
			
			while (results.next()) {
				
				ExistingTB.add(results.getInt("ID"));
				
				result = true;
				
			}
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return result;
		
	}

	private TeachingBlock buildObject(ResultSet results) {
		
		TeachingBlock tb = null;
		Staff staff = null;
		Subject subject = null;
		Room room = null;
		
		try {
			
			if (results.getInt("Subject") != 0) {
				subject = dbSubject.findSubjectID(results.getInt("Subject"));
			}
			if (results.getInt("Staff") != 0) {
				staff = dbStaff.findStaffID(results.getInt("Staff"));
			}
			if (results.getInt("Room") != 0) {
				room = dbRoom.findRoomID(results.getInt("Room"));
			}
			
			tb = new TeachingBlock (results.getInt("ID"),
									subject,
									staff,
									room);
			
		}
		catch (Exception e) {
			System.out.println("While building the TeachingBlock an exeption occured!");
			System.out.println(e);
		}
		
		return tb;
	}

	public List<Integer> getExistingTB() {
		return ExistingTB;
	}

}
